import random
import pymysql as pymysql
from faker import Faker
import tkinter as tk
from tkinter import messagebox
import faker
import pymysql as pymysql

class CustomFakerProvider(faker.providers.BaseProvider):
    def __init__(self, locales=None, **kwargs):
        super().__init__(locales, **kwargs)

        # 模拟数据，实际应用时请替换为真实数据源
        self.fujian_areas = [
            {'name': '宁德市', 'code': '350900'},
            {'name': '蕉城区', 'code': '350900350902'},
            {'name': '古田县', 'code': '350900350922'},
            {'name': '福鼎市', 'code': '350900350982'},
            {'name': '霞浦县', 'code': '350900350921'},
            {'name': '福安市', 'code': '350900350981'},
            {'name': '柘荣县', 'code': '350900350926'},
            {'name': '屏南县', 'code': '350900350923'},
            {'name': '周宁县', 'code': '350900350925'},
            {'name': '寿宁县', 'code': '350900350924'},
            {'name': '福州市', 'code': '350100'},
            {'name': '罗源县', 'code': '350100350123'},
            {'name': '闽清县', 'code': '350100350124'},
            {'name': '连江县', 'code': '350100350122'},
            {'name': '福清市', 'code': '350100350181'},
            {'name': '长乐区', 'code': '350100350112'},
            {'name': '平潭县', 'code': '350100350128'},
            {'name': '台江区', 'code': '350100350103'},
            {'name': '永泰县', 'code': '350100350125'},
            {'name': '鼓楼区', 'code': '350100350102'},
            {'name': '晋安区', 'code': '350100350111'},
            {'name': '闽侯县', 'code': '350100350121'},
            {'name': '马尾区', 'code': '350100350105'},
            {'name': '仓山区', 'code': '350100350104'},
            {'name': '莆田市', 'code': '350300'},
            {'name': '秀屿区', 'code': '350300350305'},
            {'name': '荔城区', 'code': '350300350304'},
            {'name': '仙游县', 'code': '350300350322'},
            {'name': '涵江区', 'code': '350300350303'},
            {'name': '城厢区', 'code': '350300350302'},
            {'name': '泉州市', 'code': '350500'},
            {'name': '惠安县', 'code': '350500350521'},
            {'name': '晋江市', 'code': '350500350582'},
            {'name': '洛江区', 'code': '350500350504'},
            {'name': '南安市', 'code': '350500350583'},
            {'name': '金门县', 'code': '350500350527'},
            {'name': '石狮市', 'code': '350500350581'},
            {'name': '泉港区', 'code': '350500350505'},
            {'name': '永春县', 'code': '350500350525'},
            {'name': '安溪县', 'code': '350500350524'},
            {'name': '鲤城区', 'code': '350500350502'},
            {'name': '德化县', 'code': '350500350526'},
            {'name': '丰泽区', 'code': '350500350503'},
            {'name': '厦门市', 'code': '350200'},
            {'name': '湖里区', 'code': '350200350206'},
            {'name': '思明区', 'code': '350200350203'},
            {'name': '翔安区', 'code': '350200350213'},
            {'name': '集美区', 'code': '350200350211'},
            {'name': '同安区', 'code': '350200350212'},
            {'name': '海沧区', 'code': '350200350205'},
            {'name': '漳州市', 'code': '350600'},
            {'name': '龙海区', 'code': '350600350604'},
            {'name': '漳浦县', 'code': '350600350623'},
            {'name': '东山县', 'code': '350600350626'},
            {'name': '南靖县', 'code': '350600350627'},
            {'name': '平和县', 'code': '350600350628'},
            {'name': '华安县', 'code': '350600350629'},
            {'name': '长泰区', 'code': '350600350605'},
            {'name': '芗城区', 'code': '350600350602'},
            {'name': '龙文区', 'code': '350600350603'},
            {'name': '云霄县', 'code': '350600350622'},
            {'name': '诏安县', 'code': '350600350624'},
            {'name': '龙岩市', 'code': '350800'},
            {'name': '武平县', 'code': '350800350824'},
            {'name': '长汀县', 'code': '350800350821'},
            {'name': '新罗区', 'code': '350800350802'},
            {'name': '漳平市', 'code': '350800350881'},
            {'name': '永定区', 'code': '350800350803'},
            {'name': '连城县', 'code': '350800350825'},
            {'name': '上杭县', 'code': '350800350823'},
            {'name': '三明市', 'code': '350400'},
            {'name': '沙县区', 'code': '350400350405'},
            {'name': '建宁县', 'code': '350400350430'},
            {'name': '将乐县', 'code': '350400350428'},
            {'name': '明溪县', 'code': '350400350421'},
            {'name': '宁化县', 'code': '350400350424'},
            {'name': '三元区', 'code': '350400350404'},
            {'name': '清流县', 'code': '350400350423'},
            {'name': '永安市', 'code': '350400350481'},
            {'name': '尤溪县', 'code': '350400350426'},
            {'name': '泰宁县', 'code': '350400350429'},
            {'name': '大田县', 'code': '350400350425'},
            {'name': '南平市', 'code': '350700'},
            {'name': '松溪县', 'code': '350700350724'},
            {'name': '浦城县', 'code': '350700350722'},
            {'name': '光泽县', 'code': '350700350723'},
            {'name': '建阳区', 'code': '350700350703'},
            {'name': '政和县', 'code': '350700350725'},
            {'name': '建瓯市', 'code': '350700350783'},
            {'name': '顺昌县', 'code': '350700350721'},
            {'name': '延平区', 'code': '350700350702'},
            {'name': '武夷山市', 'code': '350700350782'},
            {'name': '邵武市', 'code': '350700350781'}
            # ... 省内所有市县数据 ...
        ]

    def fujian_city(self):
        return self.random_element(self.fujian_areas)["name"]
    def fujian_city_code(self):
        return self.random_element(self.fujian_areas)
    def fujian_citycode(self):
        return self.random_element(self.fujian_areas)["code"]


# 初始化 Faker 实例并添加自定义提供商
fake = faker.Faker()
fake.add_provider(CustomFakerProvider)

# 使用自定义方法生成福建省的市县名称和编码
city_name = fake.fujian_city_code()
# city_code = fake.fujian_city_code()
print(f"城市名称: {city_name}")
# , 编码: {city_code}

# 若要批量生成多个样本
for _ in range(5):
    city_info = {"name": fake.fujian_city(), "code": fake.fujian_city_code()}
    print(city_info)

def connect_to_db():
    try:
        connection = pymysql.connect(
            host=host_entry.get(),
            user=username_entry.get(),
            password=password_entry.get(),
            db=db_entry.get(),
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        return connection
    except Exception as e:
        messagebox.showerror("错误", f"连接失败：{e}")
        return None

def on_login_click():
    # 连接数据库并执行查询验证用户身份（这里仅作示例，实际操作应加密存储和传输密码）
    with connect_to_db() as conn:
        with conn.cursor() as cursor:
            result = cursor.execute("SELECT 1")
            if result:
                messagebox.showinfo("提示", "连接成功")
            else:
                messagebox.showerror("错误", "连接失败")


def on_insert_click():
    # 连接数据库
    # con = connect_to_db()
    con = pymysql.connect(host=host_entry.get(), user=username_entry.get(), password=password_entry.get(), database=db_entry.get(), port=int(port_entry.get()))

    # 获取所有表名
    cursor = con.cursor()
    # cursor.execute("SHOW TABLES")
    # print(cursor.fetchall())
    # allTables = [table[0] for table in cursor.fetchall()]

    if not table_entry.get():
        messagebox.showerror("错误", "请输入表名")
        return

    # 换成实际要生成数据的表
    tables = table_entry.get().split(",")

    # 生成测试数据
    faker = Faker("zh_CN")
    faker.add_provider(CustomFakerProvider)

    for table in tables:
        # 获取表结构
        # cursor.execute("DESC %s" % table)
        cursor.execute(
            "SELECT column_name, data_type, column_comment FROM information_schema.COLUMNS WHERE table_name = '%s'" % table.strip())
        columnsAll = [column for column in cursor.fetchall()]
        columns = [column[0] for column in columnsAll]

        try:
            # 生成 20 条测试数据
            for i in range(20):
                data = []
                for column in columnsAll:
                    citycode = faker.fujian_city_code()
                    if "UNID" in column[0] or '_ID' in column[0] or 'ID' == column[0] or '_uuid' in column[0]:
                        data.append(faker.uuid4().replace("-", ""))
                    elif "int" in column[1] or "_number" in column[0]:
                        data.append(random.randint(0, 10000))
                    elif "AREA" in column[0] or "_POS" in column[0]:
                        data.append(citycode['code'])
                    elif "_NAME" in column[0] or "_POS_NAME" in column[0]:
                        data.append(citycode['name'])
                    elif "datetime" in column[1] or "date" in column[1]:
                        data.append(faker.date_time())
                    elif "CERTIFICATE" in column[0]:
                        data.append(faker.ssn())
                    else:
                        # data.append(faker.random_element([faker.name(), faker.email(), faker.address(), faker.phone_number()]))
                        data.append(faker.random_element([faker.company_prefix()]))

                # 插入数据
                sql = ("INSERT INTO %s (%s) VALUES (%s)" % (table, ",".join(columns), ",".join(["%s"] * len(data))), data)
                print(sql)

                cursor.execute("INSERT INTO %s (%s) VALUES (%s)" % (table, ",".join(columns), ",".join(["%s"] * len(data))),data)
        except Exception as e:
            messagebox.showerror("错误", f"插入数据失败：{e}")
            return
        messagebox.showinfo("提示", "插入数据成功")

    # 关闭数据库连接
    con.commit()
    con.close()


# 创建窗口
root = tk.Tk()
root.title("自动生成表数据V1.0")
root.geometry("900x200")
# host
host_label = tk.Label(root, text="数据库地址:")
host_label.grid(row=0, column=0)
host_entry = tk.Entry(root)
host_entry.grid(row=0, column=1)
# db
db_label = tk.Label(root, text="数据库名:")
db_label.grid(row=0, column=2)
db_entry = tk.Entry(root)
db_entry.grid(row=0, column=3)
# table
table_label = tk.Label(root, text="表名:")
table_label.grid(row=0, column=4)
table_entry = tk.Entry(root)
table_entry.grid(row=0, column=5)
# port
port_label = tk.Label(root, text="端口:")
port_label.grid(row=0, column=6)
port_entry = tk.Entry(root)
port_entry.insert(0, "3306")  # 在控件的开头插入默认值3306
port_entry.grid(row=0, column=7)


username_label = tk.Label(root, text="Username:")
username_label.grid(row=1, column=0)
username_entry = tk.Entry(root)
username_entry.grid(row=1, column=1, padx=(100,20), sticky="nsew")

password_label = tk.Label(root, text="Password:")
password_label.grid(row=2, column=0)
password_entry = tk.Entry(root, show="*")  # 显示星号代替密码
password_entry.grid(row=2, column=1, padx=(100,20), sticky="nsew")


login_button = tk.Button(root, text="测试连接", command=on_login_click)
login_button.grid(row=3,column=0, columnspan=4)

insert_button = tk.Button(root, text="生成数据", command=on_insert_click)
insert_button.grid(row=3,column=1, columnspan=4)

root.mainloop()


